use ".\data\clean\make_model.dta", clear
merge m:1 vehicle_model vehicle_make using ".data\clean\concordance_vehicle_names_to_kevindata_070215.dta", nogen
drop if new_model=="DROP"
drop if vehicle_make=="ASTO" | vehicle_make=="FERRARI" | vehicle_make=="CHECKER"
replace vehicle_model=new_model if new_model!=""
drop new_model
destring vehicle_year, replace
drop if vehicle_make==""
*g hybrid=cond(vehicle_type=="HYBRID",1,0)
replace vehicle_model="40 SERIES" if vehicle_model=="S40" & vehicle_year>2005
replace vehicle_model="80 SERIES" if vehicle_model=="S80" & vehicle_year>2005
replace vehicle_model="70 SERIES" if vehicle_model=="C70" & vehicle_year>2009
replace vehicle_model="70 SERIES" if vehicle_model=="V70" & vehicle_year>2005
replace vehicle_model="JIMMY" if vehicle_model=="S15" & vehicle_make=="GMC" & vehicle_year>1990
replace vehicle_model="C-CLASS" if vehicle_model=="230" & vehicle_make=="MERCEDES"
replace vehicle_model="C-CLASS" if vehicle_model=="300" & vehicle_make=="MERCEDES"
replace vehicle_model="C-CLASS" if vehicle_model=="C240" & vehicle_make=="MERCEDES"
replace vehicle_model="TRUCK" if vehicle_model=="K1500" & vehicle_make=="CHEVROLET"
replace vehicle_model="TRUCK" if vehicle_model=="K2500" & vehicle_make=="CHEVROLET"
replace vehicle_model="SEBRING" if vehicle_model=="SEBRING CONVERTIBLE" & vehicle_year<2004
replace vehicle_model="GRAND CHEROKEE" if vehicle_model=="CHEROKEE" & vehicle_year>2006
replace vehicle_model="MX" if strmatch(vehicle_model,"MX*")==1 & vehicle_make=="MAZDA"
replace vehicle_model="OTHER" if vehicle_model=="210"  & vehicle_make=="NISSAN"
replace vehicle_model="COROLLA" if vehicle_model=="CORONA" & vehicle_year>2002
replace vehicle_model="OTHER" if vehicle_model=="CAPTIVA" & vehicle_make=="CHEVROLET"
replace vehicle_model="OTHER" if vehicle_model=="L.U.V." & vehicle_make=="CHEVROLET"
replace vehicle_model="VAN" if vehicle_model=="SPORT VAN" & vehicle_year>1996
replace vehicle_model="OTHER" if vehicle_model=="150" & vehicle_make=="CHEVROLET"
replace vehicle_model="OTHER" if vehicle_model=="SPORTSMAN" & vehicle_make=="DODGE"
replace vehicle_model="TRUCK" if vehicle_model=="RANCHERO" & vehicle_make=="FORD"
replace vehicle_model="TRUCK" if inlist(vehicle_model,"F100","F450","F550") & vehicle_make=="FORD"
replace vehicle_model="C240" if vehicle_model=="240" & vehicle_make=="MERCEDES"
replace vehicle_model="GRAND CARAVAN" if vehicle_year>2007 & vehicle_model=="CARAVAN"
replace vehicle_model="SILVERADO" if strmatch(vehicle_model,"*C1500*")==1 & vehicle_make=="CHEVROLET" & vehicle_year>1998
replace vehicle_make="GMC" if vehicle_model=="LEMANS"
replace vehicle_make="LAND ROVER" if vehicle_make=="LANDROVER"
replace vehicle_make="MERCEDES-BENZ" if vehicle_make=="MERCEDES"
drop if vehicle_make==""
replace vehicle_model="OTHER" if vehicle_make=="ACURA" & vehicle_model=="EL" // no msrp data
replace vehicle_make="LAND ROVER" if vehicle_make=="RANGE ROVER"
replace vehicle_make="ROLLS-ROYCE" if vehicle_make=="ROLL"
replace vehicle_make="SMART" if vehicle_make=="MERCEDES-BENZ" & vehicle_model=="SMART"
replace vehicle_model="FORTWO" if vehicle_make=="SMART"
replace vehicle_model="RAM" if strmatch(vehicle_model,"*RAM*")==1 & vehicle_model!="RAM WAGON" & vehicle_make=="DODGE"
replace vehicle_model="F350" if vehicle_make=="FORD" & strmatch(vehicle_model,"*350*")==1
replace vehicle_model="Mazda3" if vehicle_model=="323" & vehicle_year>1995
replace vehicle_model="200" if vehicle_model=="200 SX" & vehicle_make=="NISSAN"
g hybrid=cond(vehicle_type=="HYBRID",1,0)
ren vehicle_year year
save ".\data\clean\make_model_msrp_premerge_clean.dta", replace







use ".data\raw\cars_for_Andrew", clear
ren modelyr year
drop if msrp==.
preserve
	collapse msrp, by(make year hybrid)
	g model="Other"
	save ".\data\clean\make_msrp_avg.dta", replace
restore
preserve
	keep if class==7 | class==8
	collapse msrp, by(make year hybrid)
	g model="TRUCK"
	save ".\data\clean\make_msrp_truck.dta", replace
restore
preserve
	keep if class==17 | class==18
	collapse msrp, by(make year hybrid)
	g model="VAN"
	save ".\data\clean\make_msrp_van.dta", replace
restore
preserve
	keep if make=="Lexus" & hybrid==1
	collapse msrp, by(year)
	g model="HYBRID"
	g make="LEXUS"
	g hybrid=1
	save ".\data\clean\make_msrp_lexushybrid.dta", replace
restore
preserve
	keep if make=="Mazda" & strmatch(model,"Rx*")==1
	collapse msrp, by(year hybrid)
	g model="RX"
	g make="MAZDA"
	save ".\data\clean\make_msrp_mazdarx.dta", replace
restore
preserve
	keep if make=="Mazda" & strmatch(model,"Mx*")==1
	collapse msrp, by(year hybrid)
	g model="RX"
	g make="MAZDA"
	save ".\data\clean\make_msrp_mazdamx.dta", replace
restore
preserve
	keep if make=="Mercedes-Benz" & strmatch(model,"*230")==1
	collapse msrp, by(year hybrid)
	g model="230"
	g make="Mercedes-Benz"
	save ".\data\clean\make_msrp_mercedes230.dta", replace
restore
replace model="Colorado" if model=="Colorado Crew Cab"
replace model="Colorado" if model=="Colorado Extended Cab"
replace model="Colorado" if model=="Colorado Regular Cab"
replace model="Titan" if model =="Titan Crew Cab"
replace model="Titan" if model =="Titan King Cab"
replace model="Silverado" if strmatch(model,"Silverado*")==1
replace model="Silverado" if strmatch(model,"Silaerado*")==1
replace model="Silverado" if strmatch(model,"Siiverado*")==1
replace model="Silverado" if strmatch(model,"Silver*")==1
replace model="C/K" if strmatch(model,"C/K*")==1
replace model="C/K" if strmatch(model,"C *")==1 & make=="Chevrolet"
replace model="C/K" if strmatch(model,"K *")==1 & make=="Chevrolet"
replace model="Mr2" if model=="Mr2 Spyder"
replace model="Ram" if strmatch(model,"*Ram*")==1 & make=="Dodge" & model!="Ram Wagon"
replace model="Frontier" if strmatch(model,"Frontier*")==1 & make=="Nissan"
replace model="Tacoma" if strmatch(model,"*Tacoma*")==1 & make=="Toyota"
replace model="Tundra" if strmatch(model,"*Tundra*")==1 & make=="Toyota"
replace model="Dakota" if strmatch(model,"*Dakota*")==1 & make=="Dodge"
replace model="C1500" if strmatch(model,"*C1500*")==1 & make=="Chevrolet"
replace model="Venture" if strmatch(model,"Venture*")==1 & make=="Chevrolet"
replace model="Venture" if strmatch(model,"Venlure*")==1 & make=="Chevrolet"
replace model="300" if model=="300Letter"
replace model="Express" if strmatch(model,"*Express*")==1 & make=="Chevrolet"
replace model="S10" if strmatch(model,"*S10*")==1 & make=="Chevrolet"
replace model="K1500" if strmatch(model,"*K1500*")==1 & make=="Chevrolet"
replace model="K2500" if strmatch(model,"*K2500*")==1 & make=="Chevrolet"
replace model="Ranger" if strmatch(model,"*Ranger*")==1 & make=="Ford"
replace model="Mx" if strmatch(model,"Mx*")==1 & make=="Mazda"
replace model="Sebring" if model=="Sebring Coupe"
replace model="Explorer Sport" if model=="Explorer Sport Trac"
replace model="Sebring" if model=="Sebring Sedan"
collapse msrp, by( make model year hybrid)
append using ".\data\clean\make_msrp_avg.dta"
append using ".\data\clean\make_msrp_truck.dta"
append using ".\data\clean\make_msrp_van.dta"
append using ".\data\clean\make_msrp_lexushybrid.dta"
append using ".\data\clean\make_msrp_mazdarx.dta"
append using ".\data\clean\make_msrp_mercedes230.dta"
ren model vehicle_model
ren make vehicle_make
replace vehicle_model=upper(vehicle_model)
replace vehicle_make=upper(vehicle_make)
replace vehicle_model=subinstr(vehicle_model," HYBRID","",.)
replace vehicle_model="F100" if vehicle_make=="FORD" & strmatch(vehicle_model,"*100*")==1
replace vehicle_model="F150" if vehicle_make=="FORD" & strmatch(vehicle_model,"*150*")==1
replace vehicle_model="F250" if vehicle_make=="FORD" & strmatch(vehicle_model,"*250*")==1
replace vehicle_model="F350" if vehicle_make=="FORD" & strmatch(vehicle_model,"*350*")==1
replace vehicle_model="F450" if vehicle_make=="FORD" & strmatch(vehicle_model,"*450*")==1
replace vehicle_model="F550" if vehicle_make=="FORD" & strmatch(vehicle_model,"*550*")==1
replace vehicle_model="SAVANA" if vehicle_make=="GMC" & strmatch(vehicle_model,"SAVANA*")==1
replace vehicle_model="SAFARI" if vehicle_make=="GMC" & strmatch(vehicle_model,"SAFARI*")==1
replace vehicle_model="SIERRA" if vehicle_make=="GMC" & strmatch(vehicle_model,"SIERRA*")==1
replace vehicle_model="MARK" if vehicle_make=="LINCOLN" & vehicle_model=="MARK LT"

replace vehicle_model="200" if vehicle_make=="200 SX" & vehicle_make=="NISSAN"
replace vehicle_model="3500" if vehicle_make=="GMC" & strmatch(vehicle_model,"*3500*")==1
replace vehicle_model="SEVEN-SERIES" if vehicle_model=="7-SERIES" & vehicle_make=="BMW"
save ".\data\clean\make_model_msrpdata_premerge.dta", replace

collapse msrp, by( vehicle_make vehicle_model year hybrid)
merge 1:m vehicle_make vehicle_model year hybrid using ".\data\clean\make_model_msrp_premerge_clean.dta"
replace vehicle_model="7-SERIES" if vehicle_model=="SEVEN-SERIES" & vehicle_make=="BMW"
preserve
	keep if _m==3
	drop _m
	save ".\data\clean\make_model_msrp_merged_year_hybrid.dta", replace
restore
keep if _m==2
drop _m msrp
tempfile temp
save `temp', replace
use ".\data\clean\make_model_msrpdata_premerge.dta", clear
collapse msrp, by( vehicle_make vehicle_model year)
merge 1:m vehicle_make vehicle_model year using `temp'
preserve
	keep if _m==3
	drop _m
	append using ".\data\clean\make_model_msrp_merged_year_hybrid.dta"
	save ".\data\clean\make_model_msrp_merged_year_hybrid.dta", replace
restore
keep if _m==2
drop _m msrp
tempfile temp
save `temp', replace
use ".\data\clean\make_model_msrpdata_premerge.dta", clear
collapse msrp, by( vehicle_make vehicle_model)
merge 1:m vehicle_make vehicle_model using `temp'
keep if _m==3
drop _m
append using ".\data\clean\make_model_msrp_merged_year_hybrid.dta"
save ".\data\clean\make_model_msrpdata_070615.dta", replace
